Motorcycle Part Sale Analysis

You’re working for a company that sells motorcycle parts, and they’ve asked for some help in analyzing their sales data!

They operate three warehouses in the area, selling both retail and wholesale. They offer a variety of parts and accept credit cards, cash, and bank transfer as payment methods. However, each payment type incurs a different fee.

The board of directors wants to gain a better understanding of wholesale revenue by product line, and how this varies month-to-month and across warehouses. You have been tasked with calculating net revenue for each product line and grouping results by month and warehouse. The results should be filtered so that only "Wholesale" orders are included.

They have provided you with access to their database, which contains the following table called sales:

Sales

Column	Data type	Description
`order_number`	`VARCHAR`	Unique order number.
`date`	`DATE`	Date of the order, from June to August 2021.
`warehouse`	`VARCHAR`	The warehouse that the order was made from— `North`, `Central`, or `West`.
`client_type`	`VARCHAR`	Whether the order was `Retail` or `Wholesale`.
`product_line`	`VARCHAR`	Type of product ordered.
`quantity`	`INT`	Number of products ordered.
`unit_price`	`FLOAT`	Price per product (dollars).
`total`	`FLOAT`	Total price of the order (dollars).
`payment`	`VARCHAR`	Payment method—`Credit card`, `Transfer`, or `Cash`.
`payment_fee`	`FLOAT`	Percentage of `total` charged as a result of the `payment` method.

Your query output should be presented in the following format:

`product_line`	`month`	`warehouse`	`net_revenue`
product_one	—	—	—
product_one	—	—	—
product_one	—	—	—
product_one	—	—	—
product_one	—	—	—
product_one	—	—	—
product_two	—	—	—
…	…	…	…

Code

import pandas as pd
import sqlite3
import sql
from itables import init_notebook_mode

Code

init_notebook_mode(all_interactive=True)

init_notebook_modetrusted

Code

df = pd.read_csv("sales.csv") 
conn = sqlite3.connect(":memory:")

Code

df.to_sql("sales", conn, index=False, if_exists="replace")

Code

# Load the SQL extension
%load_ext sql

# Register the existing in-memory SQLite connection in jupysql

%sql conn --alias sales_dataset

::: {#ff58f388 .cell customType=‘sql’ dataFrameVariableName=‘revenue_by_product_line’ executionCancelledAt=‘null’ executionTime=‘842’ initial=‘false’ integrationId=‘89e17161-a224-4a8a-846b-0adc0fe7a4b1’ lastExecutedAt=‘1703148380931’ lastScheduledRunId=‘null’ lastSuccessfullyExecutedCode=’– Start coding here

SELECT * FROM sales;’ execution_count=6}

Code

%%sql
SELECT *
FROM sales

Running query in 'sales_dataset'

index	order_number	date	warehouse	client_type	product_line	quantity	unit_price	total	payment	payment_fee
0	N1	2021-06-01T00:00:00.000	North	Retail	Breaking system	9	19.29	173.61	Cash	0.0
1	N2	2021-06-01T00:00:00.000	North	Retail	Suspension & traction	8	32.93	263.45	Credit card	0.03
2	N3	2021-06-01T00:00:00.000	North	Wholesale	Frame & body	16	37.84	605.44	Transfer	0.01
3	N4	2021-06-01T00:00:00.000	North	Wholesale	Suspension & traction	40	37.37	1494.8	Transfer	0.01
4	N5	2021-06-01T00:00:00.000	North	Retail	Frame & body	6	45.44	272.61	Credit card	0.03
5	N6	2021-06-02T00:00:00.000	North	Retail	Frame & body	1	40.41	40.41	Credit card	0.03
6	N7	2021-06-02T00:00:00.000	North	Retail	Miscellaneous	6	20.28	121.66	Credit card	0.03
7	N8	2021-06-03T00:00:00.000	North	Retail	Electrical system	9	20.5	184.54	Credit card	0.03
8	N9	2021-06-03T00:00:00.000	North	Retail	Suspension & traction	5	36.18	180.91	Credit card	0.03
9	N10	2021-06-03T00:00:00.000	North	Retail	Electrical system	5	28.33	141.67	Credit card	0.03

Truncated to displaylimit of 10.

:::

Code

%%sql
SELECT product_line,
    CASE 
        WHEN strftime('%m', date) = '06' THEN 'June'
        WHEN strftime('%m', date) = '07' THEN 'July'
        WHEN strftime('%m', date) = '08' THEN 'August'
    END AS month,
    warehouse,
    ROUND(SUM(total * (1 - payment_fee)), 2) AS net_revenue
FROM sales
WHERE client_type = 'Wholesale'
GROUP BY product_line, warehouse, month
ORDER BY product_line ASC, month ASC, net_revenue DESC;

Running query in 'sales_dataset'

product_line	month	warehouse	net_revenue
Breaking system	August	Central	3009.1
Breaking system	August	West	2475.71
Breaking system	August	North	1753.19
Breaking system	July	Central	3740.94
Breaking system	July	West	3030.39
Breaking system	July	North	2568.55
Breaking system	June	Central	3648.14
Breaking system	June	North	1472.93
Breaking system	June	West	1200.64
Electrical system	August	North	4673.99

Truncated to displaylimit of 10.